package com.sds.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sds.util.Util_DB;
import com.sds.vo.BusVO;


public class BusDAO {
	public void insertBus(BusVO bus) {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;
		
		if(conn == null) {
			System.out.println("Connection Error - [ Location : Bus ]");
		} else {
			try {			
				pstmt = conn.prepareStatement("insert into t_bus values(?, ?)");

				pstmt.setString(1, bus.getBus_id());
				pstmt.setString(2, bus.getBus_company());

				pstmt.execute();
				
				Util_DB.disconnection(null, pstmt, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public BusVO selectBus(String bus_id) {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;
		
		BusVO bus = null;

		if(conn == null) {
			System.out.println("Connection Error - [ Location : Bus ]");
		} else {
			try {			
				pstmt = conn.prepareStatement("select * from t_bus where bus_id = ?");
				
				pstmt.setString(1, bus_id);

				ResultSet rs = pstmt.executeQuery();

				while(rs.next()) {
					bus = new BusVO(rs.getString("bus_id"), rs.getString("bus_company"));
				}
				
				Util_DB.disconnection(rs, pstmt, conn);
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}

		return bus;
	}

	public ArrayList<BusVO> selectBusList() {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;		

		ArrayList<BusVO> busList = new ArrayList<BusVO>();

		if(conn == null) {
			System.out.println("Connection Error - [ Location : Bus ]");
		} else {
			try {			
				pstmt = conn.prepareStatement("select * from t_bus order by bus_id");

				ResultSet rs = pstmt.executeQuery();

				while(rs.next()) {
					busList.add( new BusVO(rs.getString("bus_id"), rs.getString("bus_company")) );
				}

				Util_DB.disconnection(rs, pstmt, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return busList;
	}

	public void updateBus(BusVO bus) {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;
		
		if(conn == null) {
			System.out.println("Connection Error - [ Location : Bus ]");
		} else {
			try {			
				pstmt = conn.prepareStatement("update t_bus set bus_company = ? where bus_id = ?");

				pstmt.setString(1, bus.getBus_company());
				pstmt.setString(2, bus.getBus_id());
				
				pstmt.executeUpdate();

				Util_DB.disconnection(null, pstmt, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void deleteBus(String bus_id) {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;
				
		if(conn == null) {
			System.out.println("Connection Error - [ Location : Bus ]");
		} else {
			try {			
				pstmt = conn.prepareStatement("delete t_bus where bus_id = ?");

				pstmt.setString(1, bus_id);
				
				pstmt.executeUpdate();

				Util_DB.disconnection(null, pstmt, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}